package com.rpj.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.rpj.entity.Book;
import com.rpj.entity.StuEntity;
import com.rpj.entity.TeaEntity;
import com.rpj.service.BookService;
import com.rpj.service.StuService;
import com.rpj.service.TeaService;


@RestController

public class ExcelUtils {
	@Autowired
	private StuService stuservice;
	@Autowired
	private TeaService teaservice;
	@Autowired
	private BookService bookservice;
@RequestMapping(value="/inExcel",method=RequestMethod.POST)
	public Object inExcel(MultipartFile file,HttpServletRequest request) {
	//��ȡ�ļ�����
	String filename = file.getOriginalFilename();
	//ʱ��ƴ�ӣ����֣�
	String newFileName=System.currentTimeMillis()+filename;
	//��ȡ�ļ�λ��
	String path = request.getServletContext().getRealPath("/upload");
	File tempFile = new File(path+File.separator+newFileName);
		try {
			file.transferTo(tempFile);

			FileInputStream in = new FileInputStream(tempFile);

			XSSFWorkbook workbook = new XSSFWorkbook(in);

			XSSFSheet  sheet = workbook.getSheetAt(0);

			int lastRowNum = sheet.getLastRowNum();
			for(int i = 1;i<=lastRowNum;i++) {
	
				XSSFRow row = sheet.getRow(i);
		
				String stuid =String.valueOf((int)row.getCell(0).getNumericCellValue());
				String stuname = row.getCell(1).getStringCellValue();
				String stupassword =   String.valueOf((int)row.getCell(2).getNumericCellValue());
				String sex = row.getCell(3).getStringCellValue();
				String status =   String.valueOf((int)row.getCell(4).getNumericCellValue());
				String department = row.getCell(5).getStringCellValue();//ϵ
				String specialty = row.getCell(6).getStringCellValue();//רҵ��
				String degree = row.getCell(7).getStringCellValue();//ѧ��
				String stuborrowmaxnum =   String.valueOf((int)row.getCell(8).getNumericCellValue());
				String stuborrowbooknum =   String.valueOf((int)row.getCell(9).getNumericCellValue());
				String stugivebooknum =   String.valueOf((int)row.getCell(10).getNumericCellValue());
				String data1 =  row.getCell(11).getStringCellValue();
				String data2 =  row.getCell(12).getStringCellValue();
				String data3 =  row.getCell(13).getStringCellValue();
		
			 //�����ݷ�װ��ʵ��
			StuEntity stuEntity = new StuEntity(stuid,stuname,stupassword,sex,status,department,specialty,degree,stuborrowmaxnum,stuborrowbooknum,stugivebooknum,data1,data2,data3);
				
			 //���뵽���ݿ�
				stuservice.save(stuEntity);
			}
			
			

		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}




@RequestMapping(value="/TeainExcel",method=RequestMethod.POST)
public Object teainExcel(MultipartFile file,HttpServletRequest request) {
//��ȡ�ļ�����
String filename = file.getOriginalFilename();
//ʱ��ƴ�ӣ����֣�
String newFileName=System.currentTimeMillis()+filename;
//��ȡ�ļ�λ��
String path = request.getServletContext().getRealPath("/upload");
File tempFile = new File(path+File.separator+newFileName);
	try {
		file.transferTo(tempFile);

		FileInputStream in = new FileInputStream(tempFile);

		XSSFWorkbook workbook = new XSSFWorkbook(in);

		XSSFSheet  sheet = workbook.getSheetAt(0);

		int lastRowNum = sheet.getLastRowNum();
		for(int i = 1;i<=lastRowNum;i++) {

			XSSFRow row = sheet.getRow(i);
	
			String teaid =String.valueOf((int)row.getCell(0).getNumericCellValue());
			String teaname = row.getCell(1).getStringCellValue();
			String teapassword =   String.valueOf((int)row.getCell(2).getNumericCellValue());
			String status =   String.valueOf((int)row.getCell(3).getNumericCellValue());
			String teadept = row.getCell(4).getStringCellValue();//ѧ��
			String teaborrowmaxnum =   String.valueOf((int)row.getCell(5).getNumericCellValue());
			String teaborrowbooknum =   String.valueOf((int)row.getCell(6).getNumericCellValue());
			String teagivebooknum =   String.valueOf((int)row.getCell(7).getNumericCellValue());
			String data1 =  row.getCell(8).getStringCellValue();
			String data2 =  row.getCell(9).getStringCellValue();
			String data3 =  row.getCell(10).getStringCellValue();
		 //�����ݷ�װ��ʵ�
			TeaEntity teaEntity = new TeaEntity(teaid,teaname,teapassword,status,teadept,teaborrowmaxnum,teaborrowbooknum,teagivebooknum,data1,data2,data3);
		 //���뵽���ݿ�
			teaservice.save(teaEntity);
		}
		
		

	} catch (IllegalStateException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return true;
}

@RequestMapping(value="/bookinExcel",method=RequestMethod.POST)
public Object bookinExcel(MultipartFile file,HttpServletRequest request) {
	//��ȡ�ļ�����
		String filename = file.getOriginalFilename();
		//ʱ��ƴ�ӣ����֣�
		String newFileName=System.currentTimeMillis()+filename;
		//��ȡ�ļ�λ��
		String path = request.getServletContext().getRealPath("/upload");
		File tempFile = new File(path+File.separator+newFileName);
			try {
				file.transferTo(tempFile);

				FileInputStream in = new FileInputStream(tempFile);

				XSSFWorkbook workbook = new XSSFWorkbook(in);

				XSSFSheet  sheet = workbook.getSheetAt(0);

				int lastRowNum = sheet.getLastRowNum();
				for(int i = 1;i<=lastRowNum;i++) {
		
					XSSFRow row = sheet.getRow(i);
	
			String bookid =String.valueOf((int)row.getCell(0).getNumericCellValue());//图书编号
			String bookname = row.getCell(1).getStringCellValue();//图书名字
			String bookauthor = row.getCell(2).getStringCellValue();//图书作者
			String bookpress = row.getCell(3).getStringCellValue();//出版社
			String booknum = String.valueOf((int)row.getCell(4).getNumericCellValue());//图书数量
			String bookcate = row.getCell(5).getStringCellValue();//ѧ��//图书类别
			String bookmessage =  row.getCell(6).getStringCellValue();//ѧ��//图书信息
			String bookprice = String.valueOf((int)row.getCell(76).getNumericCellValue());//图书价格
			String bookaddress =   row.getCell(8).getStringCellValue();//存放地址
			String onlinenum =   String.valueOf((int)row.getCell(9).getNumericCellValue());//现在馆内数量
			String borrownum =   String.valueOf((int)row.getCell(10).getNumericCellValue());//被借阅次数
	
		 //�����ݷ�װ��ʵ�
			Book book = new Book(bookid,bookname,bookauthor,bookpress,booknum,bookcate,bookmessage,bookprice,bookaddress,onlinenum,borrownum);
		 //���뵽���ݿ�
			bookservice.save(book);
		}
		
	} catch (IllegalStateException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return true;
}

}
